﻿using System;
using System.Collections.Generic;
using Npgsql;

namespace WpfPostgresql.Model
{
    class Databases
    {
        public class Tovar
        {
            public int ID { get; set; }
            public int N { get; set; }
            public string Name { get; set; }
            public int SkladID { get; set; }
            public ViewModel.MagazinsViewModel ParentMagazVm { get; set; }
            public ViewModel.SkladViewModel ParentVm { get; set; }
            public int AddedN { get; set; }
        }

        public class Magaz
        {
            public int ID { get; set; }
            public string Name { get; set; }
            public string Addres { get; set; }
        }

        public class Posilka
        {
            public int ID { get; set; }
            public string Name { get; set; }
        }

        public class Voditel
        {
            public int ID { get; set; }
            public string Name { get; set; }
        }

        public class TovarPosilka
        {
            public int TovarID { get; set; }
            public int PosilkaID { get; set; }
        }

        public class PosilkaVoditel
        {
            public int PosilkaID { get; set; }
            public int VoditelID { get; set; }
        }

        public class PosilkaMagazin
        {
            public int PosilkaID { get; set; }
            public int MagazinID { get; set; }
        }
        
        public class MagzinTovar
        {
            public int MagazinID { get; set; }
            public int TovarID { get; set; }
        }



        private const string Connetction = "Server=127.0.0.1;Port=5432;User Id=postgres;Password=333771;Database=kursovik;";

#region tovar

        public static List<Tovar> GetTovarList(ViewModel.SkladViewModel parentVm)
        {
            var tempList = new List<Tovar>();
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();
            var command = new NpgsqlCommand("select * from tovar order by name", conn);


            try
            {
                var dr = command.ExecuteReader();
                while (dr.Read())
                {
                    tempList.Add(new Tovar {ParentVm = parentVm, ID = (int)dr[0], N = (int)dr[1], Name = (string)dr[2], SkladID = (int) dr[3] });
                }

            }

            finally
            {
                conn.Close();
            }
            return tempList;
        }

        public static List<Tovar> GetTovarListWithoutAddedInMagaz(ViewModel.SkladViewModel parentVm)
        {
            var tempList = new List<Tovar>();
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();
            var command = new NpgsqlCommand("select * from tovar where id not in (select tovarid from magzin_tovar) order by name", conn);


            try
            {
                var dr = command.ExecuteReader();
                while (dr.Read())
                {
                    tempList.Add(new Tovar { ParentVm = parentVm, ID = (int)dr[0], N = (int)dr[1], Name = (string)dr[2], SkladID = (int)dr[3] });
                }

            }

            finally
            {
                conn.Close();
            }
            return tempList;
        }

        public static void AddTovar(Tovar post)
        {
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();

            var command = new NpgsqlCommand("insert into tovar (colichestvo, name, skladid) values(" + post.N +",'"+post.Name+"',"+post.SkladID+")", conn);

            try
            {
                var rowsaffected = command.ExecuteNonQuery();
            }

            finally
            {
                conn.Close();
            }
        }

        public static void DeleteTovarById(int id)
        {
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();

            var command = new NpgsqlCommand("delete from tovar where(id = "+ id + ")", conn);

            try
            {
                var rowsaffected = command.ExecuteNonQuery();
            }

            finally
            {
                conn.Close();
            }
        }

        public static void DeleteTovarInMagazinById(int id)
        {
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();

            var command = new NpgsqlCommand("delete from magzin_tovar where(tovarid = " + id + ")", conn);

            try
            {
                var rowsaffected = command.ExecuteNonQuery();
            }

            finally
            {
                conn.Close();
            }
        }

        public static void AddColTovarById(int id, int col)
        {
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();

            var command = new NpgsqlCommand("UPDATE tovar SET colichestvo = colichestvo+"+col+" WHERE id = " + id, conn);

            try
            {
                var rowsaffected = command.ExecuteNonQuery();

            }

            finally
            {
                conn.Close();
            }
        }


#endregion

#region magazin

        public static List<Magaz> GetMagazList()
        {
            var tempList = new List<Magaz>();
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();
            var command = new NpgsqlCommand("select * from magzin order by name", conn);


            try
            {
                var dr = command.ExecuteReader();
                while (dr.Read())
                {
                    tempList.Add(new Magaz {  ID = (int)dr[0],  Name = (string)dr[1], Addres = (string)dr[2] });
                }

            }

            finally
            {
                conn.Close();
            }
            return tempList;
        }
#endregion

#region Voditel
        public static List<Voditel> GetVoditelList()
        {
            var tempList = new List<Voditel>();
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();
            var command = new NpgsqlCommand("select * from voditel order by name", conn);


            try
            {
                var dr = command.ExecuteReader();
                while (dr.Read())
                {
                    tempList.Add(new Voditel { ID = (int)dr[0], Name = (string)dr[1]});
                }

            }

            finally
            {
                conn.Close();
            }
            return tempList;
        }
#endregion


#region tovar_posilka
        public static void AddtovarPosilka(TovarPosilka post)
        {
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();

            var command = new NpgsqlCommand("insert into tovar_posilka (tovarid, posilkaid) values(" + post.TovarID + "," + post.PosilkaID + ")", conn);

            try
            {
                var rowsaffected = command.ExecuteNonQuery();
            }

            finally
            {
                conn.Close();
            }
        }

        public static List<Tovar> GetTovarListByPosilkaId(int id)
        {
            var tempList = new List<Tovar>();
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();
            var command = new NpgsqlCommand("select * from tovar where id in (select tovarid from tovar_posilka where posilkaid = "+ id+ ")", conn);


            try
            {
                var dr = command.ExecuteReader();
                while (dr.Read())
                {
                    tempList.Add(new Tovar {ParentVm = null, ID = (int)dr[0], N = (int)dr[1], Name = (string)dr[2], SkladID = (int) dr[3] });
                }

            }

            finally
            {
                conn.Close();
            }
            return tempList;
        }

        public static void DeleteTovarPosilkaByPosilkaID(int id)
        {
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();

            var command = new NpgsqlCommand("delete from tovar_posilka where posilkaID = " + id, conn);

            try
            {
                var rowsaffected = command.ExecuteNonQuery();
            }

            finally
            {
                conn.Close();
            }
        }

        public static List<Tovar> GetTovarListByMagazId(int id, ViewModel.MagazinsViewModel skladViewModel)
        {
            var tempList = new List<Tovar>();
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();
            var command = new NpgsqlCommand("select * from tovar  where id in (select tovarid from magzin_tovar where magzinid = " + id + ") order by name", conn);


            try
            {
                var dr = command.ExecuteReader();
                while (dr.Read())
                {
                    tempList.Add(new Tovar { ParentMagazVm= skladViewModel, ID = (int)dr[0], N = (int)dr[1], Name = (string)dr[2], SkladID = (int)dr[3] });
                }

            }

            finally
            {
                conn.Close();
            }
            return tempList;

        }
#endregion

#region posilka

        public static int CreateNewPosilka()
        {
            var tempList = 0;
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();
            var command = new NpgsqlCommand("insert into posilka (name) values('aaas') returning id", conn);
            try
            {
                var dr = command.ExecuteReader();
                while (dr.Read())
                {
                    tempList = (int)dr[0];
                }

            }

            finally
            {
                conn.Close();
            }
            return tempList;
        }

        public static List<Posilka> GetPosilks()
        {
            var tempList = new List<Posilka>();
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();
            var command = new NpgsqlCommand("select * from posilka order by id", conn);
            try
            {
                var dr = command.ExecuteReader();
                while (dr.Read())
                {
                    tempList.Add(new Posilka{ID = (int) dr[0],Name = (string) dr[1]});
                }

            }

            finally
            {
                conn.Close();
            }
            return tempList;
        }
        public static void DeletePosilkaByPosilkaID(Posilka posilka)
        {

            DeleteTovarPosilkaByPosilkaID(posilka.ID);
            DeletePosilkaVoditelByPosilkaID(posilka.ID);
            DeletePosilkaMagzinByPosilkaID(posilka.ID);

            var conn = new NpgsqlConnection(Connetction);
            conn.Open();

            var command = new NpgsqlCommand(@"delete from posilka where id = "  + posilka.ID, conn);

            try
            {
                var rowsaffected = command.ExecuteNonQuery();

                Console.WriteLine(@"It was added {0} lines in table table1", rowsaffected);
            }

            finally
            {
                conn.Close();
            }
        }

#endregion

#region posilka_voditel
        public static void AddPosilkaVoditel(PosilkaVoditel post)
        {
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();

            var command = new NpgsqlCommand("insert into posilka_voditel (posilkaid, voditelid) values(" + post.PosilkaID + "," + post.VoditelID + ")", conn);

            try
            {
                var rowsaffected = command.ExecuteNonQuery();
            }

            finally
            {
                conn.Close();
            }
        }

        public static Voditel GetVoditelByPosilkaId(int id)
        {
            Voditel tempList = null;
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();
            var command = new NpgsqlCommand("select * from voditel where id in(select voditelid from posilka_voditel where posilkaid = "+ id+")", conn);


            try
            {
                var dr = command.ExecuteReader();
                while (dr.Read())
                {
                    tempList = new Voditel {ID = (int) dr[0], Name = (string) dr[1]};
                }

            }

            finally
            {
                conn.Close();
            }
            return tempList;
        }

        public static void DeletePosilkaVoditelByPosilkaID(int id)
        {
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();

            var command = new NpgsqlCommand("delete from posilka_voditel where posilkaID = " + id, conn);

            try
            {
                var rowsaffected = command.ExecuteNonQuery();
            }

            finally
            {
                conn.Close();
            }
        }
#endregion

#region posilka_magazin
        public static void AddPosilkaMagazin(PosilkaMagazin post)
        {
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();

            var command = new NpgsqlCommand("insert into posilka_magzin (posilkaid, magzinid) values(" + post.PosilkaID + "," + post.MagazinID + ")", conn);

            try
            {
                var rowsaffected = command.ExecuteNonQuery();
            }

            finally
            {
                conn.Close();
            }
        }

        public static Magaz GetMagazinlByPosilkaId(int id)
        {
            Magaz tempList = null;
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();
            var command = new NpgsqlCommand("select * from magzin where id in(select magzinid from posilka_magzin where posilkaid = " + id +")", conn);


            try
            {
                var dr = command.ExecuteReader();
                while (dr.Read())
                {
                    tempList = new Magaz { ID = (int)dr[0], Name = (string)dr[1], Addres = (string)dr[2]};
                }

            }

            finally
            {
                conn.Close();
            }
            return tempList;
        }
        public static void DeletePosilkaMagzinByPosilkaID(int id)
        {
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();

            var command = new NpgsqlCommand("delete from posilka_magzin where posilkaID = " + id, conn);

            try
            {
                var rowsaffected = command.ExecuteNonQuery();
            }

            finally
            {
                conn.Close();
            }
        }
#endregion

#region MagzinTovar

        public static void AddMagzinTovar(MagzinTovar post)
        {
            var conn = new NpgsqlConnection(Connetction);
            conn.Open();

            var command = new NpgsqlCommand("insert  into magzin_tovar values (" + post.MagazinID +"," + post.TovarID+ ")", conn);

            try
            {
                var rowsaffected = command.ExecuteNonQuery();
            }

            finally
            {
                conn.Close();
            }
        }

#endregion

#region other
        


#endregion
    }


}
